package com.smartdo.crawler.processor;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import com.smartdo.crawler.utils.DBUtils;
import com.smartdo.crawler.utils.RedisFactory;
import org.bson.Document;

import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 1.读取存在mongodb的商品信息
 * 2.解析商品信息
 * 3.按需求的数据结构存入mysql
 *
 * @author zhangcheng
 */
public class ProcessorData implements Runnable {

    static AtomicInteger stat = new AtomicInteger(1069358);

    public static void main(String[] args) {
//        int skip = 0;
//        int limit = 1000;
//        while (true) {
//            List<Document> dataList = MongodbUtils.getMongoDBInstance().find("test", "sumaitong", new Document(), skip, limit);
//            if (dataList.size() == 0) {
//                break;
//            }
//            for (int i = 0; i < dataList.size(); i++) {
//                new ProcessorData(dataList.get(i)).run();
//            }
//            skip = skip + limit;
//        }
        changeData();
    }

    Document document;

    public ProcessorData(Document document) {
        this.document = document;
    }

    @Override
    public void run() {
        if (RedisFactory.sismember("test:sumaitong:db:susscus", document.getObjectId("_id").toString())) {
            return;
        }
        int brandId = saveBrand(document);
        int categoryId = saveCategory(document);
        int goodsid = saveGoods(document);
        saveGoodsExtCategory(document, categoryId, goodsid);
        saveGoodsExtDetailSql(document, goodsid);
        saveGoodsImage(document, goodsid);
        int productId = saveProduct(document, brandId, categoryId);
        int itemCodeId = saveProperty("Item Code", categoryId, 1);
        saveProductBase(document, productId, itemCodeId, categoryId);
        saveProductSku(productId, document, categoryId, goodsid);
        saveGoodsDetaliImg(goodsid, document);
        saveGoodsDetali(goodsid, document);
        RedisFactory.setSetValue("test:sumaitong:db:susscus", document.getObjectId("_id").toString());
    }

    String goodsDetaliImgSql = "insert into goods_ext_detail_img(goods_id,img_url,create_time) values(?,?,now())";

    void saveGoodsDetaliImg(int goodsId, Document document) {
        List<String> imgList = (List) document.get("descImgList");
        for (String url : imgList) {
            new DBUtils().executeUpdate(goodsDetaliImgSql, goodsId, url);
        }
    }

    String goodsDetaliSql = "insert into goods_detail(goods_id,title,description,detail_url) values(?,?,?,?)";

    void saveGoodsDetali(int goodsId, Document document) {
        List<String> imgList = (List) document.get("descImgList");
        String content = document.get("descContent") + "\nPackage Size: " + document.getString("packageSize") + "\nPackage Weight: " + document.getString("weight");
        new DBUtils().executeUpdate(goodsDetaliSql, goodsId, document.get("title"), content, imgList.size() > 0 ? imgList.get(0) : "");
    }

    String goodsItemSql = "insert into goods_item(goods_id,sku_id,price,market_price,num,create_time,ratio) values(?,?,?,?,1,now(),60)";

    void saveGoodsItem(int goodsId, int skuId, String price) {
        new DBUtils().executeUpdate(goodsItemSql, goodsId, skuId, price, price);
    }

    String propertyImgSql = "INSERT INTO property_img_detail(spu_id,property_value_id,img_url,create_time,main_url) VALUES(?,?,?,NOW(),?)";

    void savePropertyImg(int productId, int propertyValueId, String url) {
        new DBUtils().executeUpdate(propertyImgSql, productId, propertyValueId, url, url.replace("50x50", "640x640"));
    }

    String productSkuSql = "insert into product_sku(id,product_id,price,name,pvalue_str,pvalue_desc,create_time,status,is_del) values(?,?,?,?,?,?,now(),1,0)";

    void saveProductSku(int productId, Document document, int categoryId, int goodsId) {
        List<Map<String, Object>> skuInfoList = (List) document.get("skuInfoList");
        Set<String> imgSet = new HashSet<>();
        for (Map<String, Object> map : skuInfoList) {
            List<String> pvalueList = (List) map.get("pvalueDesc");
            String pvalueDesc = "";
            String name = "";
            String pvalueStr = "";
            for (int i = 0; i < pvalueList.size(); i++) {
                String[] values = pvalueList.get(i).split("->");
                int propertyId = saveProperty(values[0], categoryId, 2);
                int propertyValueId = savePropertyValue(values[1], propertyId, categoryId);
                if (i == 0) {
                    pvalueStr = propertyId + ":" + propertyValueId;
                    name = values[1];
                    pvalueDesc = values[1];
                } else {
                    pvalueStr = pvalueStr + ";" + propertyId + ":" + propertyValueId;
                    name = name + "-" + values[1];
                    pvalueDesc = pvalueDesc + " " + values[1];
                }
                if (values.length == 3 && !imgSet.contains(values[2])) {
                    imgSet.add(values[2]);
                    savePropertyImg(productId, propertyValueId, values[2]);
                }
            }
            int id = getIndex();
            new DBUtils().executeUpdate(productSkuSql, id, productId, map.get("price"), name, pvalueStr, pvalueDesc);
            saveGoodsItem(goodsId, id, map.get("price").toString());
        }
    }

    synchronized int getIndex() {
        return stat.addAndGet(1);
    }

    String propertyValueSql = "insert into property_value(id,value,property_id,category_id,create_time,status) values(?,?,?,?,now(),1)";

    int savePropertyValue(String value, int propertyId, int categoryId) {
        int id;
        String code = value + propertyId + categoryId;
        if (RedisFactory.getHashValue("test:sumaitong:db:propertyValue", code) == null) {
            id = getIndex();
            new DBUtils().executeUpdate(propertyValueSql, id, value, propertyId, categoryId);
            RedisFactory.setHasValue("test:sumaitong:db:propertyValue", code, id + "");
            return id;
        } else {
            id = Convert.toInt(RedisFactory.getHashValue("test:sumaitong:db:propertyValue", code));
        }
        return id;
    }

    String productBaseSql = "insert into product_base(product_id,property_id,pvalue_id,create_time) values(?,?,?,now())";

    void saveProductBase(Document document, int productId, int propertyId, int categoryId) {
        int pvalueId = savePropertyValue(document.getString("productId"), propertyId, categoryId);
        new DBUtils().executeUpdate(productBaseSql, productId, propertyId, pvalueId);
    }

    String propertySql = "INSERT INTO property(id,NAME,category_id,property_type,create_time,STATUS) VALUES(?,?,?,?,NOW(),1)";

    int saveProperty(String name, int categoryId, int type) {
        int id;
        if (RedisFactory.getHashValue("test:sumaitong:db:property", name + categoryId) == null) {
            id = getIndex();
            new DBUtils().executeUpdate(propertySql, id, name, categoryId, type);
            RedisFactory.setHasValue("test:sumaitong:db:property", name + categoryId, id + "");
            return id;
        } else {
            id = Convert.toInt(RedisFactory.getHashValue("test:sumaitong:db:property", name + categoryId));
        }
        return id;
    }

    String productSql = "INSERT INTO product(id,NAME,brand_id,create_time,category_id,is_del) VALUES(?,?,?,NOW(),?,0)";

    int saveProduct(Document document, int brandId, int categoryId) {
        int id = getIndex();
        new DBUtils().executeUpdate(productSql, id, document.get("title"), brandId, categoryId);
        return id;
    }

    String goodsImageSql = "insert into goods_image(goods_id,url,create_time) values(?,?,now())";

    void saveGoodsImage(Document document, int goodsId) {
        List<String> imageList = (List) document.get("imgList");
        imageList.forEach(url -> {
            new DBUtils().executeUpdate(goodsImageSql, goodsId, url.replace(".jpg_50x50", ""));
        });
    }

    String goodsExtDetailSql = "insert into goods_ext_detail(goods_id,weight,package_size,specs,item_code,create_time) values(?,?,?,?,?,now())";

    void saveGoodsExtDetailSql(Document document, int goodsId) {
        Map<String, Object> map = (Map) document.get("fieldMap");
        List<String> filedList = new ArrayList<>();
        for (String key : map.keySet()) {
            filedList.add(key + ":" + map.get(key));
        }
        new DBUtils().executeUpdate(goodsExtDetailSql, goodsId, document.get("weight"), document.get("packageSize"), CollectionUtil.join(filedList, "\n"), document.get("productId"));
    }

    String goodsExtSql = "insert into goods_ext_category(goods_id,category_id,name,sales) values(?,?,?,?)";

    void saveGoodsExtCategory(Document document, int categoryId, int goodsId) {
        new DBUtils().executeUpdate(goodsExtSql, goodsId, categoryId, document.get("title"), Convert.toInt(document.getString("orderNum"), 0));
    }

    String goodsSql = "insert into goods(id,name,type,is_show,create_time,status,is_del,product_url) values(?,?,1,1,now(),1,0,?)";

    int saveGoods(Document document) {
        int id = getIndex();
        new DBUtils().executeUpdate(goodsSql, id, document.get("title"), document.get("url"));
        return id;
    }

    String categoryInfoSql = "insert into category(id,name,parent_id,level,create_time,all_type) values(?,?,?,?,now(),?)";

    int saveCategory(Document document) {
        //类目信息
        List<String> categoryList = (List) document.get("typeList");
        int categoryId = 0;
        for (int j = 0; j < categoryList.size(); j++) {
            String typeJoin = CollectionUtil.join(categoryList.subList(0, j + 1), ">");
            if (RedisFactory.getHashValue("test:sumaitong:db:category", typeJoin) == null) {
                int id = getIndex();
                new DBUtils().executeUpdate(categoryInfoSql, id, categoryList.get(j), categoryId, j + 1, typeJoin);
                RedisFactory.setHasValue("test:sumaitong:db:category", typeJoin, id + "");
                categoryId = id;
            } else {
                categoryId = Convert.toInt(RedisFactory.getHashValue("test:sumaitong:db:category", typeJoin));
            }
        }
        if (categoryId == 0) {
            categoryId = Convert.toInt(RedisFactory.getHashValue("test:sumaitong:db:category", "Other"), 0);
            if (categoryId == 0) {
                new DBUtils().executeUpdate(categoryInfoSql, 9999999, "Other", 0, 1, "Other");
                RedisFactory.setHasValue("test:sumaitong:db:category", "Other", "9999999");
                categoryId = 9999999;
            }
        }
        return categoryId;
    }

    String brandInfoSql = "insert into brand(id,name,name_en,description,url,create_time,status,is_del) value(?,?,? ,?,?,now(),1,0)";

    int saveBrand(Document document) {
        if (!RedisFactory.sismember("test:sumaitong:db:brand", document.getString("brandId"))) {
            new DBUtils().executeUpdate(brandInfoSql, document.get("brandId"), document.get("brandName"), document.get("brandName"), document.get("brandName"), document.get("brandUrl"));
            RedisFactory.setSetValue("test:sumaitong:db:brand", document.getString("brandId"));
        }
        return Convert.toInt(document.get("brandId"));
    }

    static void changeData() {
        List<Map<String, Object>> list = DBUtils.getDBUtil().getResults("SELECT id,weight,package_size  FROM goods_ext_detail");
        list.forEach(map -> {
            String wegiht = map.get("weight").toString().replaceAll("\\(.*?\\)", "").replace("kg", "(kg)").trim();
            String[] packageSize = map.get("package_size").toString().replaceAll("\\(.*?\\)", "").trim().split("x");
            String str = "";
            if (packageSize.length == 3) {
                str = str + "Length:" + packageSize[0].replace("cm", "");
                str = str + "Width:" + packageSize[1].replace("cm", "");
                str = str + "Height:" + packageSize[2].replace("cm", "");
            }
            System.out.println(map.get("id"));
            DBUtils.getDBUtil().executeUpdate("update goods_ext_detail set weight=?,package_size=? where id = ?", wegiht, str, map.get("id"));
        });
    }

}
